using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Masini
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        System.Data.SqlClient.SqlConnection bdcon; // declare the data connection
        DataSet dataset; // declare the dataset

        System.Data.SqlClient.SqlDataAdapter data_adapter; // declare the data adapter
        // the data adapter is necessary to link the dataset to the data connection
        
        int nr_rows = 0; // the number of rows in the received result
        int current_row = 0; // row index

        private void Form1_Load(object sender, EventArgs e)
        {
            bdcon = new System.Data.SqlClient.SqlConnection(); // instantiate the connection
            bdcon.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Cars;Integrated Security=True"; // specify the data source
            
            dataset = new DataSet(); // intantiate the dataset

            bdcon.Open(); // connect to the database

            string query = "SELECT * From Cars"; // prepare the SQL query
            data_adapter = new System.Data.SqlClient.SqlDataAdapter(query, bdcon); // run the query, to get the data
            data_adapter.Fill(dataset, "Cars");

            Populate(); // read from the database and display
            nr_rows = dataset.Tables["Cars"].Rows.Count; // find how many records (rows) are in the table

            bdcon.Close(); // close the database connection
        }

        private void Populate()
        {
            DataRow car_row = dataset.Tables["Cars"].Rows[current_row]; // instantiate and initialize a row of data
            tbBrand.Text = car_row.ItemArray.GetValue(1).ToString(); // get the first field of the row and display in the textbox
            tbModel.Text = car_row.ItemArray.GetValue(2).ToString();
            tbYear.Text = car_row.ItemArray.GetValue(3).ToString();
            tbNrDoors.Text = car_row.ItemArray.GetValue(4).ToString();
            tbColor.Text = car_row.ItemArray.GetValue(5).ToString();
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (current_row != nr_rows - 1) // check whether we are at the last record
            {
                current_row++; // increase row index
                Populate(); // re-display the data, from the current row
            }
            else
                MessageBox.Show("You are already at he last record.");
        }

        private void btnPrevious_Click(object sender, EventArgs e)
        {
            if (current_row>0) // check whether we are at the first record
            {
                current_row--; // decrease row index
                Populate(); // re-display the data, from the current row
            }
            else
                MessageBox.Show("You are already at the first record.");
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (current_row != 0) // check whether we are at the first record
            {
                current_row = 0; // jump to the first row
                Populate();
                MessageBox.Show("Ca Marche!       ");// re-display the data, from the current row
            }
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            if (current_row != nr_rows - 1) // check whether we are at the last record
            {
                current_row = nr_rows - 1; // jump to the last row
                Populate(); // re-display the data, from the current row
            }

        }

        private void btnSalve_Click(object sender, EventArgs e)
        {
            // we closed the data connection previously, we must reconnect
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(data_adapter);

            DataRow car_row = dataset.Tables["Cars"].NewRow(); // instantiate a new row

            // copy the data from the textboxes into the fields of the new row
            car_row[1] = tbBrand.Text;
            car_row[2] = tbModel.Text;
            car_row[3] = tbYear.Text;
            car_row[4] = tbNrDoors.Text;
            car_row[5] = tbColor.Text;
            
            dataset.Tables["Cars"].Rows.Add(car_row); // add the new row to the data source (not to the database)

            nr_rows = nr_rows + 1; // because we have more rows now
            current_row = nr_rows - 1; // make the row index point to the last one
            
            // IMPORTANT! If you do not insert the instruction below, the changes will not propagate back to the database server,
            //  because you were working only on a temporary copy (the dataset).
            //  The program would work, but the changes would not be persistent.
            data_adapter.Update(dataset, "Cars"); // update the database

            MessageBox.Show("Save successful.");
        }

        private void btnChange_Click(object sender, EventArgs e)
        {
            // we closed the data connection previously, we must reconnect
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(data_adapter);

            // Declare a new row (but do not instantiate), and set it to point to the current row.
            //  When we change the values in this row, we will actually change the values of the old row.
            // This is just for comfort; we don't have to declare a new row, we can change the values like this:
            //  dataset.Tables["Cars"].Rows[current_row][1] = tbBrand.Text;
            DataRow car_row = dataset.Tables["Cars"].Rows[current_row];

            // copy the data from the textboxes into the fields of the new row
            car_row[1] = tbBrand.Text;
            car_row[2] = tbModel.Text;
            car_row[3] = tbYear.Text;
            car_row[4] = tbNrDoors.Text;
            car_row[5] = tbColor.Text;

            data_adapter.Update(dataset, "Cars"); // update the database

            MessageBox.Show("Change successful.");            
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            // we closed the data connection previously, we must reconnect
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(data_adapter);

            dataset.Tables["Cars"].Rows[current_row].Delete(); // delete the row (not in the database)
            nr_rows--; // because we deleted a row
            current_row = 0; // set the index to the first row
            Populate(); // re-display the data, from the current row

            data_adapter.Update(dataset, "Cars"); // update the database

            MessageBox.Show("Delete successful.");
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            DataRow[] v_found_rows; // declare a vector of rows we find
            int nr_found_rows = 0; // declare and initialize a counter for rows we find

            // search for the records where "model=what the user wrote in the Model textbox"
            v_found_rows = dataset.Tables["Cars"].Select("model='" + tbModel.Text + "'");

            nr_found_rows = v_found_rows.Length; // find the number of matches

            if (nr_found_rows > 0) // check if we have at least one result
            {
                DataRow found_row; // declare a row where we will keep the fields of the found one
                found_row = v_found_rows[0]; // initialize it with the first row we found

                tbBrand.Text = found_row[1].ToString(); // get the first field and display its value in the textbox
                tbModel.Text = found_row[2].ToString();
                tbYear.Text = found_row[3].ToString();
                tbNrDoors.Text = found_row[4].ToString();
                tbColor.Text = found_row[5].ToString();
                
                MessageBox.Show(nr_found_rows.ToString()+" match(es) was found. Displaying the first.");
            }
            else
            {
                MessageBox.Show("No match was found.");
            }
        }
    }
}